﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Diagnostics;
using Wise.Util;

namespace MyDbExport
{
    class Program
    {
        public static string connectionString = @"Server=Services-PC;Initial Catalog=Wise.Framework;User ID=sa;Password=sql@lGBlQ2lekduYoqvI";

        //public static string connectionString = @"Server=.\sql12;Initial Catalog=CS6;User ID=sa;Password=111222!q";
        static void Main(string[] args)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                List<string> tables = null;
                #region 读取表名
                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = @"
DECLARE @TAB NVARCHAR(200),@SQL NVARCHAR(MAX)

DECLARE _CUR_TAB CURSOR	FOR 
SELECT name from sys.[tables]
order by name asc

OPEN [_CUR_TAB]
WHILE(1=1)
BEGIN
    FETCH NEXT FROM [_CUR_TAB] INTO @TAB;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @SQL='
SELECT
    ta.[name] AS [表名]
	,[c].[name] AS [Code]	
	,CASE WHEN [t].[name]=''varchar'' OR t.[name]=''nvarchar'' OR t.[name]=''nchar'' THEN ''[''+t.[name]+''](''+CONVERT(NVARCHAR(200), c.[prec])+'')''
	WHEN t.[name]=''decimal'' THEN ''[''+t.[name]+''](''+CONVERT(NVARCHAR(200), c.[prec])+'',''+CONVERT(NVARCHAR(200),c.[scale])+'')''
	ELSE  ''[''+t.[name]+'']''
	END	AS [数据类型]
	,CASE WHEN [c].[isnullable]=1 THEN ''NULL'' ELSE ''NOT NULL'' END AS [必填]
	
	,ISNULL( RIGHT(LEFT([e].[text],LEN(e.[text])-1),LEN(e.[text])-2),'''') AS [默认值],
    (SELECT
        [ex].[value]
     FROM
        [sys].[extended_properties] AS [ex]
     WHERE
        [ex].[major_id] = [c].[id] AND [ex].[minor_id] = [c].[colid]) AS [Title]
    ,[业务类型] = CASE WHEN EXISTS ( SELECT
                                    1
                                FROM
                                    [sys].[sysobjects] [s]
                                WHERE
                                    [s].[xtype] = ''PK'' AND [s].[parent_obj] = [c].[id] AND [s].[name] IN (
                                    SELECT
                                        [name]
                                    FROM
                                        [sys].[sysindexes]
                                    WHERE
                                        [indid] IN (
                                        SELECT
                                            [indid]
                                        FROM
                                            [sys].[sysindexkeys]
                                        WHERE
                                            [id] = [c].[id] AND [colid] = [c].[colid])) )
                  THEN ''主键''
                  ELSE ''''
             END
FROM
    [sys].[syscolumns] AS [c]
INNER JOIN [sys].[tables] AS [ta] ON [c].[id] = [ta].[object_id]
INNER JOIN (SELECT
                [name]
            ,   [system_type_id]
            FROM
                [sys].[types]
            WHERE
                [name] <> ''sysname'') AS [t] ON [c].[xtype] = [t].[system_type_id]
LEFT JOIN [sys].[syscomments] [e] ON [c].[cdefault] = [e].[id]
WHERE
    [ta].[name] = @Tab
ORDER BY
    [c].[colid];
	'
	
	EXEC SP_EXECUTESQL @SQL ,N'@Tab NVARCHAR(200)',@Tab
END
CLOSE [_CUR_TAB]
DEALLOCATE [_CUR_TAB]

";
                    using (var reader = cmd.ExecuteReader())
                    {
                        DataSet ds = DbHelper.DataReaderToDataSet(reader);
                        ExportDataSet(ds);
                    }
                }
                #endregion

            }
        }


        static void ExportDataSet(DataSet ds)
        {
            HSSFWorkbook book = new HSSFWorkbook();
            {
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    ExportTable(book, ds.Tables[i]);
                }
                string deskTop = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
                string fileName = Path.Combine(deskTop, $"{DateTime.Now.ToString("yyyy-MM-ddmm")}.xls");
                using (FileStream fs = File.Create(fileName))
                {
                    book.Write(fs);
                    fs.Close();
                }
                Process.Start(fileName);     
            }
                
        }

        static void ExportTable(HSSFWorkbook book,DataTable dataSource)
        {
            string tableName = dataSource.Rows[0][0].ToString();
            dataSource.Columns.RemoveAt(0);
            ISheet sheet = null;
            sheet = book.CreateSheet(tableName);
            //sheet.Cells.CreateRange(0, 0, dataSource.Rows.Count + 1, dataSource.Columns.Count);
            var header = sheet.CreateRow(0);
            for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
            {
                DataColumn dc = dataSource.Columns[columnIndex];
                header.CreateCell(columnIndex).SetCellValue(dc.ColumnName);
            }
            int MAX_ROW_IN_ONE_SHEET = 65000;
            for (int rowIndex = 1; rowIndex <= dataSource.Rows.Count; rowIndex++)
            {
                var row = sheet.CreateRow(rowIndex);
                for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
                {
                    string value = dataSource.Rows[rowIndex - 1][columnIndex].ToString();
                    row.CreateCell(columnIndex).SetCellValue(value);
                }
            }
        }

        class MyRow
        {
            public string Code { get; set; }

            public string 数据类型 { get; set; }

            public string 必填 { get; set; }

            public string 默认值 { get; set; }

            public string Title { get; set; }

            public string TitleKey{ get; set; }

            public string Description { get; set; }

            public string 业务类型 { get; set; }
        }
    }
}
